# coding:utf-8
# 读取excel中的模板内容
import openpyxl
import datetime


class ReadExcel(object):
    def __init__(self, fileName):
        self.wb = openpyxl.load_workbook(fileName)
        sheetName = self.wb.sheetnames[0]
        self.sheet = self.wb[sheetName]

    def read_data(self):
        maxRows = self.sheet.max_row

        reqNo = self.col_index(u'需求编号')
        mainModNo = self.col_index(u'主模块')
        subModNo = self.col_index(u'子模块')

        preNo = self.col_index(u'级别')
        funNo = self.col_index(u'设计方法')
        priNo = self.col_index(u'前提')

        titleNo = self.col_index(u'标题')
        stepNo = self.col_index(u'步骤')
        exptNo = self.col_index(u'期待结果')
        noteNo = self.col_index(u'备注')
        scriptNo = self.col_index(u'脚本')
        if reqNo==None:return False
        if mainModNo == None: return False
        if subModNo == None: return False
        if preNo == None: return False
        if priNo == None: return False
        if titleNo == None: return False
        if stepNo == None: return False
        if exptNo == None: return False
        if noteNo == None: return False
        if scriptNo == None: return False

        reqNoValue = ""
        mainModNoValue = ""
        subModNoValue = ""
        preNoValue = u"中"
        funNoValue = u'0'

        list = []
        for row in xrange(maxRows):
            if row in [0, 1]: continue
            #   需求编号的值
            reqNoValue1 = self.sheet.cell(row, reqNo + 1).value
            if reqNoValue1: reqNoValue = reqNoValue1
            mainModNoValue1 = self.sheet.cell(row, mainModNo + 1).value
            if mainModNoValue1: mainModNoValue = mainModNoValue1
            subModNoValue1 = self.sheet.cell(row, subModNo + 1).value
            if subModNoValue1: subModNoValue = subModNoValue1

            preNoValue1 = self.sheet.cell(row, preNo + 1).value
            if preNoValue1: preNoValue = preNoValue1
            funNoValue1 = self.sheet.cell(row, funNo + 1).value
            if funNoValue1: funNoValue = funNoValue1

            if funNoValue1 == u'正向用例':
                funNoValue = '0'
            elif funNoValue1 == u'异常用例':
                funNoValue = '1'
            elif funNoValue1 == u'场景用例':
                funNoValue = '2'
            elif funNoValue1 == u'其它方法':
                funNoValue = '3'
            titleNoValue = self.sheet.cell(row, titleNo + 1).value
            stepNoValue = self.sheet.cell(row, stepNo + 1).value
            exptNoValue = self.sheet.cell(row, exptNo + 1).value
            noteNoValue = self.sheet.cell(row, noteNo + 1).value
            scriptNoValue = self.sheet.cell(row, scriptNo + 1).value

            priNoValue = self.sheet.cell(row, priNo + 1).value

            if titleNoValue == None or titleNoValue == "":
                continue
            # 组装成新的数据结构
            init_data = {"case_fun": funNoValue, "autofun": scriptNoValue, "imag_desc": "",
                         "case_require_no": reqNoValue,
                         "update_time": str(datetime.datetime.now()), "case_expect": exptNoValue,
                         "case_step": stepNoValue,
                         "case_note": noteNoValue, "case_title": titleNoValue, "case_type": '0', "is_show": 0,
                         "case_second_mod": subModNoValue, "case_pre": priNoValue, "case_pri": preNoValue,
                         "last_time": str(datetime.datetime.now()),
                         "case_main_mod": mainModNoValue}
            list.append(init_data)
        return list

    def col_index(self, colName):
        index = 0
        for row in self.sheet.rows:
            for cell in row:
                if colName in cell.value:
                    return index
                index += 1
        return None

# b = ReadExcel(r'D:\HttpTest\files\templates.xlsx')
# print len(b.read_data(1,1,1))